package bean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class ScoreDTO {
	
	private ScoreVO vo=null;
	private Connection conn= null;
	
	// 페이지와 관련된 변수
	
	int listSize = 10;	// 페이지당 표시되는 행수
	int blockSize = 3;	// 힌블럭당 포시되는 페이지수
	int totSize = 0;	//	전체 게시물
	int totPage = 0;	// 전체 페이지수
	int totBlock = 0;	//전체 블럭수
	int nowPage = 1;	//현재 페이지
	int nowBlock = 1;	// 현재 블럭
	int startNo = 0;	//리스트 목록의 시작 위치
	int endNo = 0; 	//리스트 목록의 끝 위치
	int startPage = 0;	// 현재 블럭에서 표시되는 시작 블럭번호
	int endPage = 0;	// 현재 블럭에서 표시되는 끝 블럭 번호
	
	public ScoreDTO() {
		conn = new MyOracle().getConn();
	}


	public boolean insert(){			// 입력을 위한 처리 
		boolean b= false;
		/*String sql = "select mid, grade, point, scode, examdate, exam, school, locate, rank "
					+ " from member where mid=? and grade =?";*/
		String sql = "insert into score(rno, mid, grade, scode, examdate, exam, point, rank) "
				+ "values(seq_score.nextval,?,?,?,?,?,?,?)";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, vo.getMid());
			pst.setString(2, vo.getGrade());
			pst.setString(3, vo.getScode());
			pst.setString(4, vo.getExamdate());
			pst.setString(5, vo.getExam());
			pst.setInt(6, vo.getPoint());
			pst.setInt(7, vo.getRank());
			int cnt = pst.executeUpdate();
			if(cnt>0) b = true;
			pst.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return b;
	}
	
	public boolean modify(){			// 입력을 위한 처리 
		boolean b= false;
		
		String sql = "update score set "
				+ " mid=?, grade=?, scode=?, examdate=?, exam=?, point=?, rank=? "
				+ " where rno=?";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, vo.getMid());
			pst.setString(2, vo.getGrade());
			pst.setString(3, vo.getScode());
			pst.setString(4, vo.getExamdate());
			pst.setString(5, vo.getExam());
			pst.setInt(6, vo.getPoint());
			pst.setInt(7, vo.getRank());
			pst.setInt(8, vo.getRno());
			int cnt = pst.executeUpdate();
			if(cnt>0) b = true;
			pst.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return b;
	}
	
	public void page(){
			String sql=		"select count(*) cnt "
							+	"from score "
							+	"where mid like ? or exam like ?";
			
			try {
				PreparedStatement pst = conn.prepareStatement(sql);
				pst.setString(1, "%" + vo.getFind() + "%");
				pst.setString(2, "%" + vo.getFind() + "%");
				ResultSet rs = pst.executeQuery();
				if(rs.next()){
					totSize = rs.getInt("cnt");
					// 전체 페이지
					totPage = (int)Math.ceil(totSize*1.0 / listSize);
					// 전체 블럭수
					totBlock = (int)Math.ceil(totPage*1.0 / blockSize);
					//현재 블럭
					nowBlock = (int)Math.ceil(nowPage*1.0 / blockSize);
					
					// 페이지의 시작과 끝 번호
					endPage = nowBlock * blockSize;
					startPage = endPage-blockSize+1;
					// 게시물의 시작번호와 끝 번호
					endNo = nowPage * listSize;
					startNo = endNo - listSize +1;
					
					//endPage, endNo 보정 값
					if(endPage > totPage){
						endPage = totPage;
					}
					if(endNo > totSize){
						endNo = totSize;
					}
					if(startNo < 1){
						nowBlock = 1;
						startPage = 1;
						endPage = 5;
						endNo = 10;
						startNo = 1;
					}
				}
				/*
				System.out.println("<li>totSize = " + totSize);
				System.out.println("<li>totPage = " + totPage);
				System.out.println("<li>totBlock = " + totBlock);
				
				System.out.println("<li>nowBlock = " + nowBlock);
				
				System.out.println("<li>startPage = " + startPage);
				System.out.println("<li>endPage = " + endPage);
				
				System.out.println("<li>endNo = " + endNo);
				System.out.println("<li>startNo = " + startNo);
				*/
				
			} catch (Exception e) {
				// TODO: handle exception
			}
	}

	public ArrayList<ScoreVO> select(){	// 검색과 리스트를 위한 처리 
		page();
		ArrayList<ScoreVO> al = new ArrayList<ScoreVO>();
		String sql = 	"	select * from( "
						+ "	select rownum rn, c.* from( "
						+ "		select * "
						+ "		from score " 
						+ "		where mid like ? or exam like ? "
						+ "	)c "
						+ ")b "
						+ " where b.rn between ? and ?";
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, "%" + vo.getFind() + "%");
			pst.setString(2, "%" + vo.getFind() + "%");
			pst.setInt(3, startNo);
			pst.setInt(4, endNo);
			
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				
				ScoreVO v = new ScoreVO();
				v.setRno(rs.getInt("rno"));
				v.setMid(rs.getString("mid"));
				v.setExam(rs.getString("exam"));
				v.setGrade(rs.getString("grade"));
				v.setPoint(rs.getInt("point"));
				al.add(v);
			}
			rs.close();
			pst.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return al;
	}
	
	public ScoreVO selectOne(){			// view를 위한 처리 
		ScoreVO v = new ScoreVO();
		String sql = "select * from score where rno=?";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getRno());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				v.setRno(rs.getInt("rno"));
				v.setMid(rs.getString("mid"));
				v.setGrade(rs.getString("grade"));
				v.setScode(rs.getString("scode"));
				v.setExamdate(rs.getString("examdate"));
				v.setExam(rs.getString("exam"));
				v.setPoint(rs.getInt("point"));
				v.setRank(rs.getInt("rank"));
			}
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {

		}
		return v;
	}

	public boolean delete(){
		boolean b = false;
		String sql = "delete from score where rno=?";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getRno());
			int cnt = pst.executeUpdate();
			if(cnt>0) b=true;
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}		
		return b;
	}
	
	public ScoreVO getVo() {
		return vo;
	}

	public void setVo(ScoreVO vo) {
		this.vo = vo;
		this.nowPage = vo.getNowPage();
		this.nowBlock = vo.getNowBlock();
	}


	public int getTotPage() {
		return totPage;
	}


	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}


	public int getNowPage() {
		return nowPage;
	}


	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}


	public int getStartPage() {
		return startPage;
	}


	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}


	public int getEndPage() {
		return endPage;
	}


	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}


	public int getTotSize() {
		return totSize;
	}


	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}


	public int getEndNo() {
		return endNo;
	}


	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}


	public int getStartNo() {
		return startNo;
	}


	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}


	public int getTotBlock() {
		return totBlock;
	}


	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}


	public int getNowBlock() {
		return nowBlock;
	}


	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}
	

	

}
